﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;
/// <summary>
///AccessHelper 的摘要说明
/// </summary>
/// 
namespace ExameRegister.BO
{
    public class AccessHelper
    {
        private string strConnection { get; set; }
        private static AccessHelper instance = null;
        //private static SqlConnection connection = null;
        public static AccessHelper GetInstance()
        {
            if (instance == null)
                instance = new AccessHelper();
            return instance;
        }


        public AccessHelper()
        {
            //
            //TODO: 在此处添加构造函数逻辑
            //
            strConnection = System.Configuration.ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
        }
        public string GetConnectionStr()
        {
            return strConnection;
        }
        //private  string strConnection = System.Configuration.ConfigurationManager.ConnectionStrings["ZhouPuCommunityDB"].ConnectionString;
        public DataTable GetDataSet(string sql)
        {
            SqlConnection objConnection = new SqlConnection(strConnection);
            DataTable dt = new DataTable();
            try
            {
                SqlDataAdapter sda = new SqlDataAdapter(sql, objConnection);
                sda.Fill(dt);
                objConnection.Open(); return dt;
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                objConnection.Close();
            }
        }

        public DataSet ExcuteDataSet(string sql)
        {
            SqlConnection objConnection = new SqlConnection(strConnection);
            DataSet ds = new DataSet();
            try
            {
                SqlDataAdapter sda = new SqlDataAdapter(sql, objConnection);
                sda.Fill(ds);
                objConnection.Open(); return ds;
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                objConnection.Close();
            }
        }

        public DataSet ExcuteDataSet(string sql, SqlParameter[] paras)
        {

            SqlConnection objConnection = new SqlConnection(strConnection);
            DataSet ds = new DataSet();
            try
            {
                SqlDataAdapter sda = new SqlDataAdapter(sql, objConnection);
                if (paras != null)
                {
                    sda.SelectCommand.Parameters.AddRange(paras);
                }
                sda.Fill(ds);
                objConnection.Open(); return ds;
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                objConnection.Close();
            }
        }

        public DataSet ExcuteDataSet(string sql, SqlParameter[] paras, SqlConnection conn)
        {

            //SqlConnection objConnection = new SqlConnection(strConnection);
            DataSet ds = new DataSet();
            try
            {
                SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
                if (paras != null)
                {
                    sda.SelectCommand.Parameters.AddRange(paras);
                }
                sda.Fill(ds);
                return ds;
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {

            }
        }


        public DataSet ExcuteDataSet(string spname, string svalue)
        {

            SqlConnection objConnection = new SqlConnection(strConnection);
            DataSet ds = new DataSet();
            try
            {
                SqlCommand comm = new SqlCommand(spname, objConnection);
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.Add(new SqlParameter("@sqlWhere", SqlDbType.VarChar));
                comm.Parameters["@sqlWhere"].Value = svalue;
                SqlDataAdapter sda = new SqlDataAdapter();
                sda.SelectCommand = comm;
                sda.Fill(ds);
                objConnection.Open(); return ds;
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                objConnection.Close();
            }
        }

        public bool ExcultData(string sql)
        {
            SqlConnection objConnection = new SqlConnection(strConnection);
            SqlCommand objcmd = new SqlCommand(sql, objConnection);
            try
            {
                if (objConnection.State == ConnectionState.Closed) { objConnection.Open(); }
                return objcmd.ExecuteNonQuery() > 0 ? true : false;
            }
            catch (OleDbException ob)
            {
                throw ob;
            }
            finally { objConnection.Close(); }

        }
        public int ExecuteSql(String Sqlstr, SqlParameter[] param)
        {
            using (SqlConnection objConnection = new SqlConnection(strConnection))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = objConnection;
                cmd.CommandText = Sqlstr;
                cmd.Parameters.AddRange(param);
                objConnection.Open();
                cmd.ExecuteNonQuery();
                objConnection.Close();
                return 1;
            }
        }
        public int ExecuteNonQuery(string sql)
        {
            SqlConnection _Connection = new SqlConnection(strConnection);
            if (_Connection.State == ConnectionState.Closed)
                _Connection.Open();
            System.Data.SqlClient.SqlCommand com = new System.Data.SqlClient.SqlCommand(sql, _Connection);
            return com.ExecuteNonQuery();
        }

        public int ExecuteNonQuery(string sql, SqlParameter[] param = null)
        {
            SqlConnection _Connection = new SqlConnection(strConnection);
            if (_Connection.State == ConnectionState.Closed)
                _Connection.Open();           
            System.Data.SqlClient.SqlCommand com = new System.Data.SqlClient.SqlCommand(sql, _Connection);
            if (param != null)
                com.Parameters.AddRange(param);
            return com.ExecuteNonQuery();
        }


        /// <summary>
        /// 开始事务
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        public SqlTransaction BeginTransaction(out SqlConnection conn)
        {
            conn = new SqlConnection(strConnection);
            conn.Open();
            return conn.BeginTransaction();
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        /// <param name="transction"></param>
        public void EndTransaction(SqlTransaction transction)
        {
            transction.Commit();
            transction.Connection.Close();
            transction.Connection.Dispose();
        }


        public object ExecuteScalar(String Sqlstr, SqlParameter[] param = null)
        {
            using (SqlConnection objConnection = new SqlConnection(strConnection))
            {

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = objConnection;
                cmd.CommandText = Sqlstr;
                if (param != null)
                    cmd.Parameters.AddRange(param);
                objConnection.Open();
                object res = cmd.ExecuteScalar();
                objConnection.Close();
                return res;
            }
        }


    }
}
